package vg.services.data_base_manager.realization;


import java.io.File;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import vg.services.data_base_manager.data.graph.Attribute;
import vg.services.data_base_manager.data.graph.Edge;
import vg.services.data_base_manager.data.graph.Graph;
import vg.services.data_base_manager.data.graph.Vertex;
import vg.services.data_base_manager.data.header.AttributeHeader;
import vg.services.data_base_manager.data.header.EdgeHeader;
import vg.services.data_base_manager.data.header.GraphHeader;
import vg.services.data_base_manager.data.header.GraphModelHeader;
import vg.services.data_base_manager.data.header.VertexHeader;
import vg.services.main_manager.MainManager;

import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteException;
import com.almworks.sqlite4java.SQLiteJob;
import com.almworks.sqlite4java.SQLiteQueue;
import com.almworks.sqlite4java.SQLiteStatement;

public class SimpleSQLite4JavaDataBase {
	private SQLiteQueue currConnection;
	private File db_file;
	/**
	 * Constructor.
	 */
	public SimpleSQLite4JavaDataBase() throws Throwable {
		Logger.getLogger("com.almworks.sqlite4java").setLevel(Level.WARNING);
		
		File dbDir = new File("data/db/");
		if (!dbDir.exists()) {
			dbDir.mkdirs();
		}
		db_file = File.createTempFile("tmpdb", ".db", dbDir);
		this.currConnection = new SQLiteQueue(db_file);
		db_file.deleteOnExit();
		//create structure of data base
		this.currConnection.start();
		SQLiteJob<Void> job = currConnection.execute(new SQLiteJob<Void>() {
			protected Void job(SQLiteConnection connection) throws Throwable {
				initial_simpleest_commit(connection, "pragma synchronous=OFF;");
				initial_simpleest_commit(connection, "begin;");
				
				initial_simpleest_commit(connection, "drop table if exists " + DataBaseFields.GRAPH_MODEL_TABLE + ";");
				initial_simpleest_commit(connection, "drop table if exists " + DataBaseFields.GRAPH_TABLE + ";");
				initial_simpleest_commit(connection, "drop table if exists " + DataBaseFields.ATTRIBUTE_TABLE + ";");
				initial_simpleest_commit(connection, "drop table if exists " + DataBaseFields.VERTEX_TABLE + ";");
				initial_simpleest_commit(connection, "drop table if exists " + DataBaseFields.EDGE_TABLE + ";");
				
				initial_simpleest_commit(connection, "create table " + DataBaseFields.VERTEX_TABLE + " (" + 
													 DataBaseFields.VERTEX_TABLE_ID + " INT PRIMARY KEY, " + 
													 DataBaseFields.VERTEX_TABLE_GRAPH_ID + " INT, " + 
													 DataBaseFields.VERTEX_TABLE_LINK2INNER_GRAPH + " INT);");
				initial_simpleest_commit(connection, "create table " + DataBaseFields.EDGE_TABLE + " (" + 
													 DataBaseFields.EDGE_TABLE_ID + " INT PRIMARY KEY," +
													 DataBaseFields.EDGE_TABLE_GRAPH_ID + " INT, " + 
													 DataBaseFields.EDGE_TABLE_SRC_VERTEX + " INT, " + 
													 DataBaseFields.EDGE_TABLE_TRG_VERTEX + " INT);");
				initial_simpleest_commit(connection, "create table " + DataBaseFields.ATTRIBUTE_TABLE + " (" + 
													 DataBaseFields.ATTRIBUTE_TABLE_ID + " INTEGER PRIMARY KEY, " +
													 DataBaseFields.ATTRIBUTE_TABLE_OWNER_ID + " INT, " +
													 DataBaseFields.ATTRIBUTE_TABLE_OWNER_TYPE + " INT, " +
													 DataBaseFields.ATTRIBUTE_TABLE_NAME + " VARCHAR, " +
													 DataBaseFields.ATTRIBUTE_TABLE_VALUE + " VARCHAR, " +
													 DataBaseFields.ATTRIBUTE_TABLE_VALUE_TYPE + " VARCHAR);");
				initial_simpleest_commit(connection, "create table " + DataBaseFields.GRAPH_TABLE + " (" + 
													 DataBaseFields.GRAPH_TABLE_ID + " INT PRIMARY KEY, " + 
													 DataBaseFields.GRAPH_TABLE_MODEL_ID + " INT, " + 
													 DataBaseFields.GRAPH_TABLE_DIRECTED + " BOOLEAN);");
				initial_simpleest_commit(connection, "create table " + DataBaseFields.GRAPH_MODEL_TABLE + " (" +
													 DataBaseFields.GRAPH_MODEL_TABLE_ID + " INT PRIMARY KEY, " +
													 DataBaseFields.GRAPH_MODEL_TABLE_ROOT_GRAPH_ID + " INT, " +
													 DataBaseFields.GRAPH_MODEL_TABLE_NAME + " VARCHAR);");
				
				// creating of indexes
				initial_simpleest_commit(connection, "create index Idx_vertex on " + DataBaseFields.VERTEX_TABLE + "(" + 
													 DataBaseFields.VERTEX_TABLE_LINK2INNER_GRAPH + ");");
				initial_simpleest_commit(connection, "create index Idx_edge on " + DataBaseFields.EDGE_TABLE + "(" + 
													 DataBaseFields.EDGE_TABLE_SRC_VERTEX + ", " + 
													 DataBaseFields.EDGE_TABLE_TRG_VERTEX + ");");
				initial_simpleest_commit(connection, "create index Idx_attribute on " + DataBaseFields.ATTRIBUTE_TABLE + "(" + 
													 DataBaseFields.ATTRIBUTE_TABLE_OWNER_ID + ", " + 
													 DataBaseFields.ATTRIBUTE_TABLE_OWNER_TYPE + ", " +
													 DataBaseFields.ATTRIBUTE_TABLE_NAME + ");");
				initial_simpleest_commit(connection, "create index Idx_graph on " + DataBaseFields.GRAPH_MODEL_TABLE + "(" + 
													 DataBaseFields.GRAPH_MODEL_TABLE_ROOT_GRAPH_ID + ");");

				// commit
				initial_simpleest_commit(connection, "commit;");
				return null;
			}
		});
		job.complete();
		//check errors
		Throwable ex = job.getError();
		if (ex != null) {
	    	throw(ex);
		}
	}
	
	public void close() {
		currConnection.stop(false);
		db_file.delete();
	}
	
	public void addGraphModelHeaders(Collection<GraphModelHeader> headers) {
		if(headers == null || headers.isEmpty()) return;
		StringBuffer buf_request = new StringBuffer(50 * headers.size());
		ArrayList<String> requests = new ArrayList<String>();
		for(GraphModelHeader buf : headers) {
			buf_request.append("insert into " + DataBaseFields.GRAPH_MODEL_TABLE + " values(");
			buf_request.append(buf.id);
			buf_request.append(",");
			buf_request.append(buf.root_id);
			if(buf.name == null)
				buf_request.append(",null);");
			else {
				buf_request.append(",'");
				buf_request.append(buf.name);
				buf_request.append("');");
			}
			requests.add(buf_request.toString());
			buf_request.setLength(0);
		}
		commit_requests(requests);
		requests.clear();
	}
	
	public void addGraphHeaders(Collection<GraphHeader> headers) {
		if(headers == null || headers.isEmpty()) return;
		StringBuffer buf_request = new StringBuffer(50 * headers.size());
		ArrayList<String> requests = new ArrayList<String>();
		for(GraphHeader buf : headers) {
			buf_request.append("insert into " + DataBaseFields.GRAPH_TABLE + " values(");
			buf_request.append(buf.id);
			buf_request.append(",");
			buf_request.append(buf.graph_model_id);
			buf_request.append(",'" + buf.directed + "');");
			requests.add(buf_request.toString());
			buf_request.setLength(0);
		}
		commit_requests(requests);
		requests.clear();
	}
	
	public void addVertexHeaders(Collection<VertexHeader> headers) {
		if(headers == null || headers.isEmpty()) return;
		StringBuffer buf_request = new StringBuffer(50 * headers.size());
		ArrayList<String> requests = new ArrayList<String>();
		for(VertexHeader buf : headers) {
			buf_request.append("insert into " + DataBaseFields.VERTEX_TABLE + " values(");
			buf_request.append(buf.id);
			buf_request.append(",");
			buf_request.append(buf.subgraph_id);
			buf_request.append(",");
			buf_request.append(buf.link2inner_graph);
			buf_request.append(");");
			requests.add(buf_request.toString());
			buf_request.setLength(0);
		}
		commit_requests(requests);
		requests.clear();
	}
	
	public void addEdgeHeaders(Collection<EdgeHeader> headers) {
		if(headers == null || headers.isEmpty()) return;
		StringBuffer buf_request = new StringBuffer(50 * headers.size());
		ArrayList<String> requests = new ArrayList<String>();
		for(EdgeHeader buf : headers) {
			buf_request.append("insert into " + DataBaseFields.EDGE_TABLE + " values(");
			buf_request.append(buf.id);
			buf_request.append(",");
			buf_request.append(buf.subgraph_id);
			buf_request.append(",");
			buf_request.append(buf.source_id);
			buf_request.append(",");
			buf_request.append(buf.target_id);
			buf_request.append(");");
			requests.add(buf_request.toString());
			buf_request.setLength(0);
		}
		commit_requests(requests);
		requests.clear();
	}
	
	public void addAttributeHeaders(Collection<AttributeHeader> headers) {
		if(headers == null || headers.isEmpty()) return;
		StringBuffer buf_request = new StringBuffer(50 * headers.size());
		ArrayList<String> requests = new ArrayList<String>();
		for(AttributeHeader buf : headers) {
			buf_request.append("insert into " + DataBaseFields.ATTRIBUTE_TABLE + " values(");
			buf_request.append(buf.id);
			buf_request.append(",");
			buf_request.append(buf.owner_id);
			buf_request.append(",");
			buf_request.append(buf.owner_type);
			buf_request.append(",'");
			buf_request.append(buf.name);
			buf_request.append("','");
			buf_request.append(buf.value);
			if(buf.type != null)
				buf_request.append("','" + DataBaseTypeParser.class2string(buf.type) + "');");
			else
				buf_request.append("', null);");
			requests.add(buf_request.toString());
			buf_request.setLength(0);
		}
		commit_requests(requests);
		requests.clear();
	}
//=============================================================================
//--------------MODIFYING METHODS----------------------------------------------
	public boolean modifyGraphHeader(GraphModelHeader newGraphModelHeader) {
		if (newGraphModelHeader == null) return false;
		
		String request = "UPDATE " + DataBaseFields.GRAPH_MODEL_TABLE + 
						 " SET " + DataBaseFields.GRAPH_MODEL_TABLE_NAME + "='" + newGraphModelHeader.name + "', " + 
								   DataBaseFields.GRAPH_MODEL_TABLE_ROOT_GRAPH_ID + "=" + newGraphModelHeader.root_id + 
						 " WHERE " + DataBaseFields.GRAPH_MODEL_TABLE_ID + "=" + newGraphModelHeader.id + ";";
		
		return commit_one_request(request);
	};
	
	public synchronized boolean modifyVertexHeader(VertexHeader newVertexHeader) {
		if (newVertexHeader == null) return false;
		
		String request = "UPDATE " + DataBaseFields.VERTEX_TABLE + 
						 " SET " + DataBaseFields.VERTEX_TABLE_GRAPH_ID + "='" + newVertexHeader.subgraph_id + "', " + 
								   DataBaseFields.VERTEX_TABLE_LINK2INNER_GRAPH + "=" + newVertexHeader.link2inner_graph + 
						 " WHERE " + DataBaseFields.VERTEX_TABLE_ID + "=" + newVertexHeader.id + ";";
		
		return commit_one_request(request);		
	}
	
	public synchronized boolean modifyAttributeHeader(AttributeHeader newAttributeHeader) {
		if (newAttributeHeader == null) return false;
		
		String request = "UPDATE " + DataBaseFields.ATTRIBUTE_TABLE + 
						 " SET " + DataBaseFields.ATTRIBUTE_TABLE_VALUE + "='" + newAttributeHeader.value + "', " + 
								   DataBaseFields.ATTRIBUTE_TABLE_VALUE_TYPE + "='" + DataBaseTypeParser.class2string(newAttributeHeader.type) + "'" + 
						 " WHERE " + DataBaseFields.ATTRIBUTE_TABLE_ID + "=" + newAttributeHeader.id + ";";
		
		return commit_one_request(request);		
	}
	
//=============================================================================
//-----------GET DATA----------------------------------------------------------	
	public GraphModelHeader getGraphModelHeader(int graphId) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.GRAPH_MODEL_TABLE+" s1 " +
						 "WHERE s1." + DataBaseFields.GRAPH_MODEL_TABLE_ID + "=" + Integer.valueOf(graphId).toString() + ";";
		
		List<List<Object>>data = executeSQLRequest(request);
		if(data == null || data.isEmpty()) {
			return null;
		}
		List<Object> first_row = data.get(0);

		Integer id = (Integer)first_row.get(0);
		Integer root_id = (Integer)first_row.get(1);
		String name = (String)first_row.get(2);
		
		GraphModelHeader gh = new GraphModelHeader(id, root_id, name);
		return gh;
	}
	
	public GraphHeader getGraphHeader(int graphId) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.GRAPH_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.GRAPH_TABLE_ID + "=" + Integer.valueOf(graphId).toString() + ";";
		
		List<List<Object>>data = executeSQLRequest(request);
		if(data == null || data.isEmpty()) {
			return null;
		}
		List<Object> first_row = data.get(0);

		Integer id = (Integer)first_row.get(0);
		Integer g_id = (Integer)first_row.get(1);
		Boolean directed = Boolean.valueOf(first_row.get(2).toString());
		
		GraphHeader sg = new GraphHeader(id, g_id, directed);
		return sg;		
	}
	
	public VertexHeader getVertexHeader(int vertexId) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.VERTEX_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.VERTEX_TABLE_ID + "=" + Integer.valueOf(vertexId).toString() + ";";

		List<List<Object>>data = executeSQLRequest(request);
		if(data == null || data.isEmpty()) {
			return null;
		}
		List<Object> first_row = data.get(0);

		Integer id = (Integer)first_row.get(0);
		Integer subgraph_id = (Integer)first_row.get(1);
		Integer link2inner_graph = (Integer)first_row.get(2);
		
		VertexHeader gh = new VertexHeader(id, subgraph_id);
		gh.link2inner_graph = link2inner_graph;
		return gh;
	}
	
	public EdgeHeader getEdgeHeader(int edgeId) {
		String request = "SELECT * " + 
						 "FROM " + DataBaseFields.EDGE_TABLE + " s1 " + 
						 "WHERE s1." + DataBaseFields.EDGE_TABLE_ID + "=" + Integer.valueOf(edgeId).toString() + ";";

		List<List<Object>> data = executeSQLRequest(request);
		if (data == null || data.isEmpty()) {
			return null;
		}
		List<Object> first_row = data.get(0);

		Integer id = (Integer) first_row.get(0);
		Integer subgraph_id = (Integer) first_row.get(1);
		Integer src_id = (Integer) first_row.get(2);
		Integer trg_id = (Integer) first_row.get(3);

		EdgeHeader eh = new EdgeHeader(id, subgraph_id);
		eh.source_id = src_id;
		eh.target_id = trg_id;
		return eh;
	}
	
	public AttributeHeader getAttributeHeader(int attributeId) {
		String request = " SELECT * " +
						 " FROM " + DataBaseFields.ATTRIBUTE_TABLE + " s1 " +
						 " WHERE s1." + DataBaseFields.ATTRIBUTE_TABLE_ID + "=" + Integer.valueOf(attributeId).toString() + ";";

		List<List<Object>>data = executeSQLRequest(request);
		if(data == null || data.isEmpty()) {
			return null;
		}
		List<Object> first_row = data.get(0);

		int id = (Integer)first_row.get(0);
		int owner_id = (Integer)first_row.get(1);
		int owner_type = (Integer)first_row.get(2);
		String name = (String)first_row.get(3);
		String value = (String)first_row.get(4);
		String type = (String)first_row.get(5);
		
		AttributeHeader ah = new AttributeHeader(id, owner_id, owner_type);
		ah.name = name;
		ah.value = value;
		ah.type = DataBaseTypeParser.string2class(type);
		return ah;
	}
	
	public Map<Integer, Attribute> getAttributes4Vertex_Map(int vertexId) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.ATTRIBUTE_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.ATTRIBUTE_TABLE_OWNER_ID + "=" + Integer.valueOf(vertexId).toString() + " and " + 
						 			   DataBaseFields.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + AttributeHeader.DEF_VERTEX_OWNER_TYPE + ";";

		List<List<Object>>data = executeSQLRequest(request);
		Map<Integer, Attribute> attributes = new HashMap<Integer, Attribute>();
		if(data != null) {
			for(List<Object> buf : data) {
				// initialize attribute
				Attribute a = new Attribute((String)buf.get(3), (String)buf.get(4), DataBaseTypeParser.string2class((String)buf.get(5)));
				a.setStorableId((Integer)buf.get(0));
				// add to result
				attributes.put(a.getStorableId(), a);
			}
		}
		
		return attributes;
	}

	public List<Attribute> getAttributes4Vertex_List(int vertexId) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.ATTRIBUTE_TABLE+" s1 " +
						 "WHERE s1." + DataBaseFields.ATTRIBUTE_TABLE_OWNER_ID + "=" + Integer.valueOf(vertexId).toString() + " and " + 
						 			   DataBaseFields.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + AttributeHeader.DEF_VERTEX_OWNER_TYPE + ";";
		
		List<List<Object>>data = executeSQLRequest(request);
		List<Attribute> attributes = new ArrayList<Attribute>();
		if(data != null) {
			for(List<Object> buf : data) {
				// initialize attribute
				Attribute a = new Attribute((String)buf.get(3), (String)buf.get(4), DataBaseTypeParser.string2class((String)buf.get(5)));
				a.setStorableId((Integer)buf.get(0));
				// add to result
				attributes.add(a);
			}
		}
		
		return attributes;
	}
	
	public Map<Integer, Attribute> getAttributes4Edge_Map(int edgeId) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.ATTRIBUTE_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.ATTRIBUTE_TABLE_OWNER_ID + "=" + Integer.valueOf(edgeId).toString() + " and " + 
						 			   DataBaseFields.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + AttributeHeader.DEF_EDGE_OWNER_TYPE + ";";
		
		List<List<Object>>data = executeSQLRequest(request);
		Map<Integer, Attribute> attributes = new HashMap<Integer, Attribute>();
		if(data != null) {
			for(List<Object> buf : data) {
				// initialize attribute
				Attribute a = new Attribute((String)buf.get(3), (String)buf.get(4), DataBaseTypeParser.string2class((String)buf.get(5)));
				a.setStorableId((Integer)buf.get(0));
				// add to result
				attributes.put(a.getStorableId(), a);
			}
		}
		
		return attributes;
	}
	
	public List<Attribute> getAttributes4Edge_List(int edgeId) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.ATTRIBUTE_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.ATTRIBUTE_TABLE_OWNER_ID + "=" + Integer.valueOf(edgeId).toString() + " and " + 
						 			   DataBaseFields.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + AttributeHeader.DEF_EDGE_OWNER_TYPE + ";";

		List<List<Object>>data = executeSQLRequest(request);
		List<Attribute> attributes = new ArrayList<Attribute>();
		if(data != null) {
			for(List<Object> buf : data) {
				// initialize attribute
				Attribute a = new Attribute((String)buf.get(3), (String)buf.get(4), DataBaseTypeParser.string2class((String)buf.get(5)));
				a.setStorableId((Integer)buf.get(0));

				attributes.add(a);
			}
		}
		return attributes;
	}
	
	public Vertex getVertex(int vertexId) {
		VertexHeader vh = getVertexHeader(vertexId);
		List<Attribute> attr = getAttributes4Vertex_List(vertexId);
		
		Vertex sv = new Vertex(attr);
		sv.setStorableId(vh.id);
		
		// TODO: We don't know directed or undirected this inner graph
		Graph g = sv.createInnerGraph(false);
		g.setStorableId(vh.link2inner_graph);
		
		return sv;
	}
	
	public Map<Integer, Vertex> getVertices(int graphId) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.VERTEX_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.VERTEX_TABLE_GRAPH_ID + "=" + Integer.valueOf(graphId).toString() + ";";
		
		List<List<Object>>data = executeSQLRequest(request);
		Map<Integer, Vertex> vertices = new HashMap<Integer, Vertex>();
		if(data != null) {
			for(List<Object> buf : data) {
				int id = (Integer)buf.get(0);
				List<Attribute> a = getAttributes4Vertex_List(id);
				Vertex v = new Vertex(a);
				v.setStorableId(id);
				
				// TODO: We don't know directed or undirected this inner graph
				Graph g = v.createInnerGraph(false);
				g.setStorableId((Integer)buf.get(2));
				vertices.put(v.getStorableId(), v);
			}
		}
		return vertices;
	}
	
	public Edge getEdge(int edgeId) {
		EdgeHeader eh = getEdgeHeader(edgeId);
		
		Vertex src = getVertex(eh.source_id);
		Vertex trg = getVertex(eh.target_id);
		
		List<Attribute> attr = getAttributes4Edge_List(edgeId);
		Edge se = new Edge(src, trg, attr);
		se.setStorableId(edgeId);
		
		return se;
	}
	
	public Map<Integer, Edge> getEdges(int subGraphId) {
		String request = "SELECT * " + 
						 "FROM " + DataBaseFields.EDGE_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.EDGE_TABLE_GRAPH_ID + " = " + Integer.valueOf(subGraphId).toString() + ";";

		List<List<Object>>data = executeSQLRequest(request);
		Map<Integer, Vertex> vertices = getVertices(subGraphId);
		Map<Integer, Edge> edges = new HashMap<Integer, Edge>();
		if(data != null) {
			for(List<Object> buf : data) {
				int id = (Integer)buf.get(0);
				List<Attribute> a = getAttributes4Edge_List(id);
				Vertex src = vertices.get((Integer)buf.get(2));
				Vertex trg = vertices.get((Integer)buf.get(3));
				Edge e = new Edge(src, trg, a);
				e.setStorableId(id);
				
				edges.put(e.getStorableId(), e);
			}
		}
		return edges;
	}
	
	public Graph getSubGraph(List<Integer> vertexId) {
		if(vertexId == null || vertexId.isEmpty()) return null;
		Integer first_element = vertexId.get(0);
		
		VertexHeader vh = getVertexHeader(first_element);
		Graph old_sub_graph = getGraph(vh.subgraph_id);
		
		// filter edges
		Set<Edge> new_edges = new HashSet<Edge>();
		for(Edge buf : old_sub_graph.getEdges()) {
			if(vertexId.contains(buf.getSource().getStorableId()) && 
				 vertexId.contains(buf.getTarget().getStorableId())) {
				new_edges.add(buf);
			}
		}
		
		// filter vertices
		Set<Vertex> new_vertices = new HashSet<Vertex>();
		for(Vertex buf : old_sub_graph.getVertices()) {
			if(vertexId.contains(buf.getStorableId())) {
				new_vertices.add(buf);
			}
		}
		
		// create result subgraph and return it
		Graph new_sub_graph = new Graph(new_vertices, new_edges, old_sub_graph.isDirected());
		new_sub_graph.setStorableId(old_sub_graph.getStorableId());
		
		return new_sub_graph;
	}
	
	public Graph getGraph(int graphId) {	
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.GRAPH_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.GRAPH_TABLE_ID + " = " + Integer.valueOf(graphId).toString() + ";";

		List<List<Object>>data = executeSQLRequest(request);
		if(data == null || data.isEmpty()) {
			return null;
		}

		Map<Integer, Vertex> vertices = getVertices(graphId);
		Map<Integer, Edge> edges = get_edges(graphId, vertices);
		
		List<Object> first_row = data.get(0);

		Integer id = (Integer)first_row.get(0);
		Boolean directed = Boolean.valueOf(first_row.get(2).toString());
		
		Graph sg = new Graph(vertices.values(), edges.values(), directed);
		sg.setStorableId(id);
		
		return sg;
	}
	
	public Graph getRootGraph(int graphModelId) {
		GraphModelHeader gh = getGraphModelHeader(graphModelId);
		if(gh != null) {
			if(gh.root_id < 0) {
				MainManager.log.printError("No root key for graph = " + gh.name);
				return null;
			} else {
				return getGraph(gh.root_id);
			}
		}
		return null;
	}
	
	public Attribute getAttribute(int attrId) {	
		String request = " SELECT * " +
						 " FROM " + DataBaseFields.ATTRIBUTE_TABLE + " s1 " +
						 " WHERE s1." + DataBaseFields.ATTRIBUTE_TABLE_ID + " = " + Integer.valueOf(attrId).toString() + ";";

		List<List<Object>>data = executeSQLRequest(request);
		if(data == null || data.isEmpty()) {
			return null;
		}

		for(List<Object> buf : data) {
			int id = (Integer)buf.get(0);
			String name = (String)buf.get(3);
			String value = (String)buf.get(4);
			String type = (String)buf.get(5);
			
			Attribute attr = new Attribute(name, value, DataBaseTypeParser.string2class(type));
			attr.setStorableId(id);
			
			return attr;
		}		
		return null;
	}	
	
	/**
	 * This method executes SQL request. 
	 * @param request - SQL request.
	 */
	public List<List<Object>> executeSQLRequest(final String request) {
		SQLiteJob<List<List<Object>>> job = this.currConnection.execute(new SQLiteJob<List<List<Object>>>() {
			protected List<List<Object>> job(SQLiteConnection connection) throws Throwable {
				SQLiteStatement currStatement = connection.prepare(request, false);
				List<List<Object>> data = new ArrayList<List<Object>>();
				while (currStatement.step()) {
					ArrayList<Object> row = new ArrayList<Object>();
					for (int i = 0; i < currStatement.columnCount(); i++) {
						row.add(currStatement.columnValue(i));
					}
					data.add(row);
				}
				currStatement.dispose();
				return data;
			}
		});
		long startTime = new Date().getTime(); // [DEBUG PERFORMANCE]
		List<List<Object>> data = job.complete();
		long finishTime = new Date().getTime(); // [DEBUG PERFORMANCE]
		MainManager.log.printDebug("Execute request: \n   " + request + "\nTime: " + ((finishTime - startTime)/ 1000.0) + " sec");
		Throwable ex = job.getError();
		if (ex != null) {
			MainManager.log.printException(ex);
			return null;
		}
		return data;
	}

//=============================================================================
//--------------Methods for working with attributes----------------------------
	public Map<String, Class<?>> getVertexAttributes4GraphModel(int graphModelId) {
		String request = " SELECT a." + DataBaseFields.ATTRIBUTE_TABLE_NAME + ", a." + DataBaseFields.ATTRIBUTE_TABLE_VALUE_TYPE +
						 " FROM " + DataBaseFields.GRAPH_TABLE + " g, " + DataBaseFields.VERTEX_TABLE + " v, " + DataBaseFields.ATTRIBUTE_TABLE + " a " + 
						 " WHERE " + "g." + DataBaseFields.GRAPH_TABLE_MODEL_ID + "=" + Integer.valueOf(graphModelId).toString() + 
						            " and v." + DataBaseFields.VERTEX_TABLE_GRAPH_ID + "=" + "g." + DataBaseFields.GRAPH_TABLE_ID + 
						            " and a." + DataBaseFields.ATTRIBUTE_TABLE_OWNER_ID + "=" + "v." + DataBaseFields.VERTEX_TABLE_ID + 
						            " and a." + DataBaseFields.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + Integer.toString(DataBaseFields.ATTRIBUTE_TABLE_VERTEX_OWNER_TYPE) + ";";
		
		List<List<Object>> data = executeSQLRequest(request);
		Map<String, Class<?>> map = new HashMap<String, Class<?>>();
		if (data != null) {
			for (List<Object> buf : data) {
				String name = (String)buf.get(0);
				String type_str = (String)buf.get(1);
				Class<?> type = DataBaseTypeParser.string2class(type_str);
				if (map.containsKey(name)) {
					Class<?> type_exist = map.get(name);
					if (type_exist != type) {
						map.put(name, DataBaseTypeParser.string2class(DataBaseTypeParser.STRING));
					}
				} else {
					map.put(name, type);
				}
			}
		}
		
		return map;
	}
	
	public Map<String, Class<?>> getEdgeAttributes4GraphModel(int graphModelId) {
		String request = " SELECT a." + DataBaseFields.ATTRIBUTE_TABLE_NAME + ", a." + DataBaseFields.ATTRIBUTE_TABLE_VALUE_TYPE +
						 " FROM " + DataBaseFields.GRAPH_TABLE + " g, " + DataBaseFields.EDGE_TABLE + " e, " + DataBaseFields.ATTRIBUTE_TABLE + " a " + 
						 " WHERE " + "g." + DataBaseFields.GRAPH_TABLE_MODEL_ID + "=" + Integer.valueOf(graphModelId).toString() + 
						            " and e." + DataBaseFields.EDGE_TABLE_GRAPH_ID + "=" + "g." + DataBaseFields.GRAPH_TABLE_ID + 
						            " and a." + DataBaseFields.ATTRIBUTE_TABLE_OWNER_ID + "=" + "e." + DataBaseFields.EDGE_TABLE_ID + 
						            " and a." + DataBaseFields.ATTRIBUTE_TABLE_OWNER_TYPE + "=" + Integer.toString(DataBaseFields.ATTRIBUTE_TABLE_EDGE_OWNER_TYPE) + ";";
		
		List<List<Object>> data = executeSQLRequest(request);
		Map<String, Class<?>> map = new HashMap<String, Class<?>>();
		if (data != null) {
			for (List<Object> buf : data) {
				String name = (String)buf.get(0);
				String type_str = (String)buf.get(1);
				Class<?> type = DataBaseTypeParser.string2class(type_str);
				if (map.containsKey(name)) {
					Class<?> type_exist = map.get(name);
					if (type_exist != type) {
						map.put(name, DataBaseTypeParser.string2class(DataBaseTypeParser.STRING));
					}
				} else {
					map.put(name, type);
				}
			}
		}
		
		return map;
	}
	
//=============================================================================
//-----------PRIVATE METHODS---------------------------------------------------
	private Map<Integer, Edge> get_edges(int graph_id, Map<Integer, Vertex> vertices) {
		String request = "SELECT * " +
						 "FROM " + DataBaseFields.EDGE_TABLE + " s1 " +
						 "WHERE s1." + DataBaseFields.EDGE_TABLE_GRAPH_ID + " = " + Integer.valueOf(graph_id).toString() + ";";
		List<List<Object>>data = executeSQLRequest(request);
		Map<Integer, Edge> edges = new HashMap<Integer, Edge>();
		if(data != null) {
			for(List<Object> buf : data) {
				int id = (Integer)buf.get(0);
				List<Attribute> a = getAttributes4Edge_List(id);
				Vertex src = vertices.get((Integer)buf.get(2));
				Vertex trg = vertices.get((Integer)buf.get(3));
				Edge e = new Edge(src, trg, a);
				e.setStorableId(id);
				edges.put(e.getStorableId(), e);
			}
		}
		return edges;
	}
	private boolean commit_requests(final List<String> requests) {
		try {
			SQLiteJob<Object> job = this.currConnection.execute(new SQLiteJob<Object>() {
				protected Object job(SQLiteConnection connection) throws Throwable {
					SQLiteStatement currStatement = null;
					currStatement = connection.prepare("BEGIN;", false);
					currStatement.stepThrough();
					currStatement.dispose();
					
					for(String buf : requests) {
						currStatement = connection.prepare(buf, false);
						currStatement.stepThrough();
						currStatement.dispose();
					}
					
					currStatement = connection.prepare("COMMIT;", false);
					currStatement.stepThrough();
					currStatement.dispose();
					return null;
				}
			});
			job.complete();
			if (job.getError() != null) {
				throw(job.getError());
			}
		} catch (Throwable ex) {
			MainManager.log.printException(ex);
			MainManager.windowMessage.warningMessage(ex.getMessage(), "Adding to database");
			return false;
		}
		return true;
	}
	private boolean commit_one_request(final String request) {
		try {
			SQLiteJob<Object> job = this.currConnection.execute(new SQLiteJob<Object>() {
				protected Object job(SQLiteConnection connection) throws Throwable {
					SQLiteStatement currStatement = null;
					currStatement = connection.prepare("BEGIN;", false);
					currStatement.stepThrough();
					currStatement.dispose();
					
					currStatement = connection.prepare(request.toString(), false);
					currStatement.stepThrough();
					currStatement.dispose();
					
					currStatement = connection.prepare("COMMIT;", false);
					currStatement.stepThrough();
					currStatement.dispose();
					return null;
				}
			});
			job.complete();
			if (job.getError() != null) {
				throw(job.getError());
			}
		} catch (Throwable ex) {
			MainManager.log.printException(ex);
			MainManager.windowMessage.warningMessage(ex.getMessage(), "Adding to database");
			return false;
		}
		return true;
	}
	
	private void initial_simpleest_commit(SQLiteConnection connection, String request) throws SQLiteException {
		SQLiteStatement st = connection.prepare(request, false);
		st.stepThrough();
		st.dispose();
	}
}
